package com.ruiyangwang.ListenBook.repository;

import com.ruiyangwang.ListenBook.dataObject.TidToCountDo;
import com.ruiyangwang.ListenBook.util.DButil;
import com.ruiyangwang.ListenBook.util.Log;
import lombok.SneakyThrows;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @author syzygy
 * @date 2022/07/16 10:14
 **/
public class RelationRepo {
    @SneakyThrows
    public List<TidToCountDo> selectCountByTidListGroupByTidList(List<Integer> tidList) {
        String sqlFormat ="select tid, count(*) as ref_count from relations where tid in (%s) group by tid order by tid";
        StringBuilder sb = new StringBuilder();
        for (Integer integer : tidList) {
            sb.append(integer).append(", ");
        }
        sb.delete(sb.length() - 2,sb.length());
        String sql = String.format(sqlFormat,sb.toString());

        List<TidToCountDo> list =new ArrayList<>();
        try(Connection c = DButil.connection()){
            try(PreparedStatement ps = c.prepareStatement(sql)){
                Log.println("打印sql" + ps);
                try(ResultSet res = ps.executeQuery()){
                    while(res.next()){
                        TidToCountDo tidToCountDo = new TidToCountDo(
                            res.getInt("tid"),
                            res.getInt("ref_count")
                        );
                        list.add(tidToCountDo);
                    }
                }
            }
        }
        return list;
    }
@SneakyThrows
    public List<Integer> selectListByAid(int aid) {
        String sql = "select tid from relations where aid = ? order by rid";
        List<Integer> tidList = new ArrayList<>();
        try(Connection c = DButil.connection()) {
            try (PreparedStatement ps = c.prepareStatement(sql)) {
                ps.setInt(1, aid);
                Log.println(ps);
                try (ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        tidList.add(rs.getInt("tid"));
                    }
                    Log.println(tidList);
                    return tidList;
                }
            }
        }
    }
@SneakyThrows
    public void insertBatch(String aid, List<Integer> tidList) {
        String sqlFormat = "insert into relations (aid, tid) values %s";
        StringBuffer sb = new StringBuffer();
        for (Integer i: tidList) {
            sb.append("(").append(aid).append(" ,").append(i).append(")").append(",");
        }
        sb.delete(sb.length() - 1,sb.length());
        String sql = String.format(sqlFormat,sb);
        try(Connection c = DButil.connection()) {
            try (PreparedStatement ps = c.prepareStatement(sql)) {
                Log.println(ps);
                ps.execute();
            }
        }
    }
}